package cn.kgc.jdbc.util;

import cn.kgc.jdbc.entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/8/30
 * 动态sql的使用
 * 业务场景：
 * 搜索功能:
 *      搜索关键词  ：  用户名   手机号  根据用户传递参数个数的不同 生成不同的sql语句  动态sql
 *             1、用户名  select *  from  user where username = ?
 *             2.手机号  select *  from  user where tel = ?
 *             3. 用户名+手机号  select *  from  user where tel = ? and username = ?
 *             4. 没有任何参数的传递   select *  from  user
 *
 *             String  sql =    select *  from  user where 1==1
 *
 *             if(username!=null){
 *                  sql +=" and username = ?"
 *             }
 *
 *             if(tel!=null){
 *                    sql +=" and  tel = ?"
 *            }
 *
 *
 *            sql  执行
 */
public class TestSQL {


    public static void main(String[] args) {


        String sql = " select *  from  user where '1'='1' ";

        String username = "lucy";
        String tel = "88888";

        //创建集合保存参数
        ArrayList<Object> params = new ArrayList<>();

        if (username!=null){
            sql+=" and username = ?";
            params.add(username);
        }

        if(tel!=null){
            sql+=" and tel = ?";
            params.add(tel);
        }

        System.out.println(sql);
        System.out.println(params);

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class),params.toArray());
            System.out.println("users = " + users);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
